/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package personal.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import personal.obj.Book;
import personal.utility.DBUtility;

/**
 *
 * @author PHATNBSE61057
 */
public class BookDAO implements Serializable {

    private final DBUtility utility = new DBUtility();

    public BookDAO() {
    }

    //<editor-fold defaultstate="collapsed" desc="SEARCHING FUNCTION SECTOR">
    public List<Book> getAllBook() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> result = new ArrayList<Book>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Book";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookID = rs.getString("BookID");
                String categoryID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, categoryID, bookName, bookLink,
                        bookThumb, bookImg, bookPrice, bookPages, bookFormat,
                        bookAuthor, bookPublishedDate, bookISBN,
                        bookDetailedEdition, bookDescription);
                result.add(book);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public Book getBookByBookID(String bookIDSearch) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Book WHERE BookID = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, bookIDSearch);
            rs = pst.executeQuery();
            if (rs.next()) {
                String bookID = rs.getString("BookID");
                String categoryID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, categoryID, bookName, bookLink,
                        bookThumb, bookImg, bookPrice, bookPages, bookFormat,
                        bookAuthor, bookPublishedDate, bookISBN,
                        bookDetailedEdition, bookDescription);
                return book;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Book> getBookByISBN(String ISBN) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> result = new ArrayList<Book>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Book WHERE BookISBN LIKE ?";
            pst = con.prepareStatement(query);
            pst.setString(1, "%" + ISBN + "%");
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookID = rs.getString("BookID");
                String categoryID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, categoryID, bookName, bookLink,
                        bookThumb, bookImg, bookPrice, bookPages, bookFormat,
                        bookAuthor, bookPublishedDate, bookISBN,
                        bookDetailedEdition, bookDescription);
                result.add(book);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Book> getBookByName(String bookNameSearch) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> result = new ArrayList<Book>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Book WHERE BookName LIKE ?";
            pst = con.prepareStatement(query);
            pst.setString(1, "%" + bookNameSearch + "%");
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookID = rs.getString("BookID");
                String categoryID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, categoryID, bookName, bookLink,
                        bookThumb, bookImg, bookPrice, bookPages, bookFormat,
                        bookAuthor, bookPublishedDate, bookISBN,
                        bookDetailedEdition, bookDescription);
                result.add(book);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<String> getListBookFormat() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<String> result = new ArrayList<String>();
        try {
            con = utility.makeConnection();
            String query = "SELECT Book.BookFormat FROM Book GROUP BY Book.BookFormat";
            pst = con.prepareStatement(query);
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookFormat = rs.getString("BookFormat");
                result.add(bookFormat);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Book> getBookByCategoryID(String categoryIDSearch) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> result = new ArrayList<Book>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Book WHERE CategoryID = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, categoryIDSearch);
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookID = rs.getString("BookID");
                String categoryID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, categoryID, bookName, bookLink,
                        bookThumb, bookImg, bookPrice, bookPages, bookFormat,
                        bookAuthor, bookPublishedDate, bookISBN,
                        bookDetailedEdition, bookDescription);
                result.add(book);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    public List<Book> getBookByCategoryName(String categoryName) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        Book book = null;
        List<Book> result = new ArrayList<Book>();
        try {
            con = utility.makeConnection();
            String query = "SELECT * FROM Book b, Category c WHERE "
                    + "b.CategoryID = c.CategoryID AND"
                    + " c.CategoryName= ?";
            pst = con.prepareStatement(query);
            pst.setString(1, categoryName);
            rs = pst.executeQuery();
            while (rs.next()) {
                String bookID = rs.getString("BookID");
                String categoryID = rs.getString("CategoryID");
                String bookName = rs.getString("BookName");
                String bookLink = rs.getString("BookLink");
                String bookThumb = rs.getString("BookThumb");
                String bookImg = rs.getString("BookImg");
                String bookPrice = rs.getString("BookPrice");
                String bookPages = rs.getString("BookPages");
                String bookFormat = rs.getString("BookFormat");
                String bookAuthor = rs.getString("BookAuthor");
                String bookPublishedDate = rs.getString("BookPublishedDate");
                String bookISBN = rs.getString("BookISBN");
                String bookDetailedEdition = rs.getString("BookDetailedEdition");
                String bookDescription = rs.getString("BookDescription");
                book = new Book(bookID, categoryID, bookName, bookLink,
                        bookThumb, bookImg, bookPrice, bookPages, bookFormat,
                        bookAuthor, bookPublishedDate, bookISBN,
                        bookDetailedEdition, bookDescription);
                result.add(book);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return null;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="UPDATING FUNCTION SECTOR">
    public boolean updateBookByBookID(Book bookUpdate) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged;
        try {
            con = utility.makeConnection();
            //Không được thay đổi bookID và categoryID
            String query = "UPDATE Book SET BookName = ?, BookLink = ?, "
                    + "BookPrice = ?, BookPages = ?,"
                    + " BookFormat = ?, BookAuthor = ?, BookPublishedDate = ?, "
                    + "BookISBN = ?, BookDetailedEdition = ?, BookDescription = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, bookUpdate.getBookName());
            pst.setString(2, bookUpdate.getBookLink());
            pst.setString(3, bookUpdate.getBookPrice());
            pst.setString(4, bookUpdate.getBookPages());
            pst.setString(5, bookUpdate.getBookFormat());
            pst.setString(6, bookUpdate.getBookAuthor());
            pst.setString(7, bookUpdate.getBookPublishedDate());
            pst.setString(8, bookUpdate.getBookISBN());
            pst.setString(9, bookUpdate.getBookDetailedEdition());
            pst.setString(10, bookUpdate.getBookDescription());
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="ADDING FUNCTION SECTOR">
    public boolean insertNewBook(Book insertBook) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged = 0;
        try {
            con = utility.makeConnection();
            //Không được thay đổi bookID và categoryID
            String query = "INSERT INTO Book (BookID, CategoryID, BookName, BookLink, "
                    + "BookThumb, BookImg, BookPrice, BookPages, BookFormat, "
                    + "BookAuthor, BookPublishedDate, BookISBN, "
                    + "BookDetailedEdition, BookDescription) "
                    + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            pst = con.prepareStatement(query);
            pst.setString(1, insertBook.getBookID());
            pst.setString(2, insertBook.getCategoryID());
            pst.setString(3, insertBook.getBookName());
            pst.setString(4, insertBook.getBookLink());
            pst.setString(5, insertBook.getBookThumb());
            pst.setString(6, insertBook.getBookImg());
            pst.setString(7, insertBook.getBookPrice());
            pst.setString(8, insertBook.getBookPages());
            pst.setString(9, insertBook.getBookFormat());
            pst.setString(10, insertBook.getBookAuthor());
            pst.setString(11, insertBook.getBookPublishedDate());
            pst.setString(12, insertBook.getBookISBN());
            pst.setString(13, insertBook.getBookDetailedEdition());
            pst.setString(14, insertBook.getBookDescription());
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    //</editor-fold>

    //<editor-fold defaultstate="collapsed" desc="DELETING FUNCTION SECTOR">
    public boolean deleteBook(String bookID) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged = 0;
        try {
            con = utility.makeConnection();
            //Không được thay đổi bookID và categoryID
            String query = "DELETE FROM Book WHERE BookID = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, bookID);
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }

    public boolean deleteAllBookInCategory(String categoryID) {
        Connection con = null;
        PreparedStatement pst = null;
        int rowChanged = 0;
        try {
            con = utility.makeConnection();
            //Không được thay đổi bookID và categoryID
            String query = "DELETE FROM Book WHERE CategoryID = ?";
            pst = con.prepareStatement(query);
            pst.setString(1, categoryID);
            rowChanged = pst.executeUpdate();
            if (rowChanged > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (pst != null) {
                    pst.close();
                }
                if (con != null) {
                    con.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return false;
    }
    //</editor-fold>
}
